package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 
 * @author 胡伟
 *  模型层
 *	提供与数据库交互的封装方法
 */
public class DAO {
	
	/**
	 * 爬虫程序结束后,立即导入数据库时调用该函数, 将会将默认文件中的数据导入数据库中
	 * @throws SQLException
	 */
	public static void pushIntoDBFromLocalFile() throws SQLException {
		pushIntoDBFromLocalFile("data.txt");// data.txt 是爬虫结果的默认保存路径
	}
	
	
	/**
	 * 把从Searcher中爬到的所有program信息一次性全部导入数据库中
	 * @param programs
	 * @throws SQLException
	 */
	public static void pushIntoDBFromLocalFile(String filePath) throws SQLException {
		//从本地文件拉取数据
		List<Program> programs = PullFromFile.pull(filePath);
		
		//操作数据库
		Connection con = DBUtil.getConnection();
		String sql = "INSERT INTO program(Id, country, university, school, program_name, homepage,"+
	            "location, email, phone_number, degree, deadline_with_aid,deadline_without_aid)"
	                +" values(?,?,?,?,?,?,?,?,?,?,?,?)";
	        //预编译
	        PreparedStatement ptmt = con.prepareStatement(sql); //预编译SQL，减少sql执行
		for(Program p:programs)
		{
			ptmt.setString(1,p.getId());
			ptmt.setString(2,p.getCountry());
			ptmt.setString(3,p.getUniversity());
			ptmt.setString(4,p.getSchool());
			ptmt.setString(5,p.getProgramName());
			ptmt.setString(6,p.getHomepage());
			ptmt.setString(7,p.getLocation());
			ptmt.setString(8,p.getEmail());
			ptmt.setString(9,p.getPhoneNumber());
			ptmt.setString(10,p.getDegree());
			ptmt.setString(11,p.getDeadlineWithAid());
			ptmt.setString(12,p.getDeadlineWithoutAid());
			
			ptmt.execute();
		}
	}
	
	
	/**
	 * 将 记录 p 加入数据库中 
	 * @param p
	 * @throws SQLException
	 */
	public static void addProgram(Program p) throws SQLException {
		Connection con = DBUtil.getConnection();
		String sql = "INSERT INTO program(Id, country, university, school, program_name, homepage,"+
	            "location, email, phone_number, degree, deadline_with_aid,deadline_without_aid)"
	                +" values(?,?,?,?,?,?,?,?,?,?,?,?)";
	        //预编译
	        PreparedStatement ptmt = con.prepareStatement(sql); //预编译SQL，减少sql执行

			ptmt.setString(1,p.getId());
			ptmt.setString(2,p.getCountry());
			ptmt.setString(3,p.getUniversity());
			ptmt.setString(4,p.getSchool());
			ptmt.setString(5,p.getProgramName());
			ptmt.setString(6,p.getHomepage());
			ptmt.setString(7,p.getLocation());
			ptmt.setString(8,p.getEmail());
			ptmt.setString(9,p.getPhoneNumber());
			ptmt.setString(10,p.getDegree());
			ptmt.setString(11,p.getDeadlineWithAid());
			ptmt.setString(12,p.getDeadlineWithoutAid());
			
			ptmt.execute();
	}
	
	
	/**
	 * 对 传入的记录 p 进行完全匹配, 找到后删除数据库中相符的记录,传入记录必须存在
	 * @param p
	 * @throws SQLException
	 */
	public static void deleteProgram(Program p) throws SQLException {
		Connection con = DBUtil.getConnection();
		String sql = "DELETE FROM program WHERE "+
	            "Id=? AND country=? AND university=? AND school=? AND program_name=? AND homepage=? AND "
				+"location=? AND email=? AND phone_number=? AND degree=? AND deadline_with_aid=? AND deadline_without_aid=?";
	        //预编译
	        PreparedStatement ptmt = con.prepareStatement(sql); //预编译SQL，减少sql执行

			ptmt.setString(1,p.getId());
			ptmt.setString(2,p.getCountry());
			ptmt.setString(3,p.getUniversity());
			ptmt.setString(4,p.getSchool());
			ptmt.setString(5,p.getProgramName());
			ptmt.setString(6,p.getHomepage());
			ptmt.setString(7,p.getLocation());
			ptmt.setString(8,p.getEmail());
			ptmt.setString(9,p.getPhoneNumber());
			ptmt.setString(10,p.getDegree());
			ptmt.setString(11,p.getDeadlineWithAid());
			ptmt.setString(12,p.getDeadlineWithoutAid());
			
			ptmt.execute();
	}
	
	
	/**
	 * 查询与传入字符串匹配的记录, 匹配范围是记录的所有字段, 可能返回集合长度为 0 
	 * @param queryStatment
	 * @return
	 * @throws SQLException
	 */
	public static List<Program> queryProgram(String queryStatment) throws SQLException {
		Connection con = DBUtil.getConnection();
		String sql = "SELECT * FROM program WHERE "+
	            "Id LIKE ? OR country LIKE ? OR university LIKE ? OR school LIKE ? OR program_name LIKE ? OR homepage LIKE ? OR "
				+"location LIKE ? OR email LIKE ? OR phone_number LIKE ? OR degree LIKE ? OR deadline_with_aid LIKE ? OR deadline_without_aid LIKE ?";
	        //预编译
	        PreparedStatement ptmt = con.prepareStatement(sql); //预编译SQL，减少sql执行

			for(int i=1;i<=12;i++)
			{
				ptmt.setString(i,"%"+queryStatment+"%");
			}			
			
			ResultSet rs = ptmt.executeQuery();
			List<Program> programs = new ArrayList<Program>();
			while(rs.next()) {
				Program p = new Program();
				p.setId(rs.getString(1));
				p.setCountry(rs.getString(2));
				p.setUniversity(rs.getString(3));
				p.setSchool(rs.getString(4));
				p.setProgramName(rs.getString(5));
				p.setHomepage(rs.getString(6));
				p.setLocation(rs.getString(7));
				p.setEmail(rs.getString(8));
				p.setPhoneNumber(rs.getString(9));
				p.setDegree(rs.getString(10));
				p.setDeadlineWithAid(rs.getString(11));
				p.setDeadlineWithoutAid(rs.getString(12));
				
				programs.add(p);
			}
			return programs;
	}
}
